//
//  DataBaseHandle.m
//  CatMusic
//
//  Created by dllo on 15/4/28.
//  Copyright (c) 2015年 dllo. All rights reserved.
//

#import "DataBaseHandle.h"

@implementation DataBaseHandle

+(instancetype)shareDataBaseHandle
{
    static DataBaseHandle *dataBaseHandle = nil;
    static dispatch_once_t oneToTake;
    dispatch_once(&oneToTake, ^{
        dataBaseHandle = [[DataBaseHandle alloc] init];
    });
    return dataBaseHandle;
}

// 打开数据库
- (void)openDB
{
    
    // 先获取沙盒路径
    NSArray *sanBox = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *sanBoxPath = sanBox[0];
    
    // 拼接一个数据库的路径,如果这个路径下没有数据库,则会创建一个数据库,并且打开这个数据库,如果有数据库了,就直接打开
    NSString *dbPath = [sanBoxPath stringByAppendingPathComponent:@"currentMusic.plist"];
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);     // 将字符串改成C语言中不可变字符串,并根据后面的指针地址存储
    if (result == SQLITE_OK) {
        NSLog(@"数据库开启成功");
        NSLog(@"%@", dbPath);
    }
    else
    {
        NSLog(@"开启失败");
    }
    
}

// 创建一张数据库的表
- (void)createTable
{
    // 第一件事,先写sql语句
    NSString *sqlStr = @"CREATE table if not exists music(ID integer primary key autoincrement, song_name text, songe_url text, pic_url text, singer_name text, lyric_url)";
    
    // 执行SQL语句
    int result = sqlite3_exec(dbPoint, [sqlStr UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }
    else
    {
        NSLog(@"创建表失败");
    }
}

// 插入一个学生
- (void)insertsMusic:(BaseModel *)music
{
     NSString *insertMusic = [NSString stringWithFormat: @"insert into music(song_name, songe_url, pic_url, singer_name, lyric_url) values('%@', '%@', '%@', '%@', '%@',)", music.song_name, music.songe_url, music.pic_url, music.singer_name, music.lyric_url];
    // 执行sql语句
    int result = sqlite3_exec(dbPoint, [insertMusic UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"插入学生成功");
    }
    else
    {
        NSLog(@"插入学生失败");
    }
}


// 删除学生信息
- (void)deleteMusic:(NSString *)song_name
{
    NSString *deleteSql = [NSString stringWithFormat:@"delete from music where song_name = %@", song_name];
    int result = sqlite3_exec(dbPoint, [deleteSql UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功!");
    }
    else
    {
        NSLog(@"删除失败!");
    }
}

// 查询所有的学生,若指定数据,则加参数
- (NSMutableArray *)selectMusic
{
    // 查询的逻辑
    // 1.先从本地数据库中取出所有的数据
    // 2.然后将取出的每条数据赋值给model
    // 3.最后存入数组,返回给调用的类
    // *是通配符,表示查询某个条件下数据库中所有字段的内容
    NSString *selectSQL = @"select * from music";
    // 需要创建一个跟随指针,用来循环遍历数据库中表的每行数据
    sqlite3_stmt *stmt = nil;
    // 第三个参数:查询语句字数限制,-1为不限制
    int result = sqlite3_prepare_v2(dbPoint, [selectSQL UTF8String], -1, &stmt, nil);
    // 这个方法相当于数据库,sql, 跟随指针关联在一起,一同完成查询操作
    // 初始化装学生的大数组
    NSMutableArray *musicArr = [NSMutableArray array];
    if(result == SQLITE_OK)
    {
        NSLog(@"查询成功!");
        // 开始遍历查询的数据库的每一行
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 让跟随指针以行进行查询遍历,如果没有行,则停止循环
            // 满足循环条件,则逐列取出内容
            const unsigned char *song_name = sqlite3_column_text(stmt, 0);
            const unsigned char *songe_url = sqlite3_column_text(stmt, 1);
            const unsigned char *pic_url = sqlite3_column_text(stmt, 2);
            const unsigned char *singer_name = sqlite3_column_text(stmt, 3);
            const unsigned char *lyric_url = sqlite3_column_text(stmt, 4);
            
            // 把取出来的内容进行类型的转换
            NSString *musicSong_name = [NSString stringWithUTF8String:(const char *)song_name];
            NSString *musicSonge_url = [NSString stringWithUTF8String:(const char *)songe_url];
            NSString *musicPic_url = [NSString stringWithUTF8String:(const char *)pic_url];
            NSString *musicSinger_name = [NSString stringWithUTF8String:(const char *)singer_name];
            NSString *musicLyric_url = [NSString stringWithUTF8String:(const char *)lyric_url];
            BaseModel *music1 = [[BaseModel alloc] init];
            music1.song_name = musicSong_name;
            music1.songe_url = musicSonge_url;
            music1.pic_url = musicPic_url;
            music1.singer_name = musicSinger_name;
            music1.lyric_url = musicLyric_url;
            [musicArr addObject:music1];
            [music1 release];
        }
    }
    else
    {
        NSLog(@"查询失败");
    }
    return musicArr;
}

// 删除数据库的表
- (void)dropTable
{
    NSString *dropSql = @"drop table music";
    int result = sqlite3_exec(dbPoint, [dropSql UTF8String], nil, nil, nil);
    if (result == SQLITE_OK) {
        NSLog(@"表删除成功!");
    }
    else
    {
        NSLog(@"表删除成功!");
    }
}

// 关闭数据库
- (void)closeDB
{
    int result = sqlite3_close(dbPoint);
    if (result == SQLITE_OK) {
        NSLog(@"关闭数据库成功!");
    }
    else
    {
        NSLog(@"关闭数据库失败!");
    }
}

@end
